﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using E.Model;
using X.DALSqlServer;

namespace E.DAL
{
    public class StudyDAL
    {
        /// <summary>
        /// 获取学习记录简单信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public StudyLogModel GetStudyShortLogByID(int id)
        {
            string sql = "SELECT ID,CourseID,Chapters FROM E_StudyLog WHERE ID = " + id;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                StudyLogModel sim = new StudyLogModel();
                if (dr.Read())
                {
                    sim.ID = (int)dr["ID"];
                    sim.CourseID = (int)dr["CourseID"];
                    sim.Chapters = dr["Chapters"].ToString();
                }
                else
                    sim.ID = -1;
                return sim;
            }
        }

        /// <summary>
        /// 添加学习记录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddStudyLog(StudyLogModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 

            };
            return 1;
        }

        /// <summary>
        /// 更新学习记录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateStudyLog(StudyLogModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ID", model.ID),
                new SqlParameter("@Chapters", model.Chapters),
                new SqlParameter("@Progress", model.Progress)
            };
            string sql = "UPDATE E_StudyLog SET Chapters = @Chapters, Progress = @Progress WHERE ID = @ID";
            return SqlHelper.ExecuteNonQueryBySql(sql,parms);
        }

        /// <summary>
        /// 获取用户学习日志
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public StudyLogListModel GetStudyLogByCondition(SearchStudyLogModel searchModel, int pageSize, int pageIndex)
        {
            StudyLogListModel listModel = new StudyLogListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserID", searchModel.UserID),
                new SqlParameter("@CourseName", searchModel.CourseName),
                new SqlParameter("@CourseCategoryID", searchModel.CourseCategoryID),
                new SqlParameter("@StartFromTime", searchModel.StartFromTime),
                new SqlParameter("@StartToTime", searchModel.StartToTime),
                new SqlParameter("@EndFromTime", searchModel.EndFromTime),
                new SqlParameter("@EndToTime", searchModel.EndToTime),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<StudyLogForListModel> list = new List<StudyLogForListModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getUserStudyLogByCondition", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        StudyLogForListModel slflm = new StudyLogForListModel();
                        slflm.ID = (int)dr["ID"];
                        slflm.ClassCode = dr["ClassCode"].ToString();
                        slflm.CourseName = dr["CourseName"].ToString();
                        slflm.TeacherName = dr["TeacherName"].ToString();
                        slflm.CourseCategoryName = dr["CourseCategoryName"].ToString();
                        slflm.StartTime = (DateTime)dr["StartTime"];
                        slflm.EndTime = (DateTime)dr["EndTime"];
                        slflm.SchoolHour = (decimal)dr["SchoolHour"];
                        slflm.IsFull = (bool)dr["IsFull"];
                        slflm.Progress = (int)dr["Progress"];
                        list.Add(slflm);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        /// <summary>
        /// 查询学员学习进度（统计分析）
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public StudyLogListModel SearchStudyLog(SearchStudyLogModel searchModel, int pageSize, int pageIndex)
        {
            StudyLogListModel listModel = new StudyLogListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ClassCode", searchModel.ClassCode),
                new SqlParameter("@CourseID", searchModel.CourseID),
                new SqlParameter("@CertificateID", searchModel.CertificateID),
                new SqlParameter("@UserName", searchModel.UserName),
                new SqlParameter("@AreaID", searchModel.AreaID),
                new SqlParameter("@TeacherID", searchModel.TeacherID),
                new SqlParameter("@EntryFromDate", searchModel.EntryFromDate),
                new SqlParameter("@EntryToDate", searchModel.EntryToDate),
                new SqlParameter("@LoginID", searchModel.LoginID),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<StudyLogForListModel> list = new List<StudyLogForListModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getProgress", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        StudyLogForListModel slflm = new StudyLogForListModel();
                        slflm.ID = (int)dr["ID"];
                        slflm.ClassCode = dr["ClassCode"].ToString();
                        slflm.LoginID = dr["LoginID"].ToString();
                        slflm.UserName = dr["UserName"].ToString();
                        slflm.Progress = (int)dr["Progress"];
                        slflm.CourseName = dr["CourseName"].ToString();
                        slflm.CertificateName = dr["CertificateName"].ToString();
                        list.Add(slflm);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        #region 内训

        /// <summary>
        /// 获取用户内训课程学习进度
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="courseid"></param>
        /// <returns></returns>
        public int GetUserInternalStudyProcess(int userid, int courseid)
        {
            string sql = "SELECT Process FROM E_Internal_StudyLog WHERE UserID = " + userid + " AND CourseID = " + courseid;
            return Convert.ToInt32(SqlHelper.ExecuteScalarBySql(sql));
        }

        /// <summary>
        /// 更新进度
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="courseid"></param>
        /// <param name="projectid"></param>
        /// <param name="process"></param>
        /// <returns></returns>
        public int UpdateUserInternalProcess(int userid, int courseid, int projectid, int process)
        {
            string sql = "UPDATE E_Internal_StudyLog SET Process = " + process + " WHERE UserID = " + userid + " AND CourseID = " + courseid + " AND ProjectID = " + projectid;
            return SqlHelper.ExecuteNonQueryBySql(sql);
        }

        /// <summary>
        /// 获取用户学习进度
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="courseid"></param>
        /// <returns></returns>
        public IList<InternalStudyModel> GetUserInternalStudyList(int userid, int courseid)
        {
            IList<InternalStudyModel> list = new List<InternalStudyModel>();
            string sql = "SELECT a.ID, a.CourseID, a.ProjectID, a.UserID, a.Process, b.Name AS CourseName, c.Name AS ProjectName FROM E_Internal_StudyLog AS a INNER JOIN  E_Dict_Course AS b ON a.CourseID = b.ID INNER JOIN E_Dict_Certificate AS c ON a.ProjectID = c.ID WHERE b.DelFlag = 0 AND a.UserID = " + userid;
            if (courseid != -1)
            {
                sql += " AND a.CourseID = " + courseid;
            }
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    InternalStudyModel model = new InternalStudyModel();
                    model.ID = (int)dr["ID"];
                    model.CourseID = (int)dr["CourseID"];
                    model.CourseName = dr["CourseName"].ToString();
                    model.ProjectID = (int)dr["ProjectID"];
                    model.ProjectName = dr["ProjectName"].ToString();
                    model.Process = (int)dr["Process"];
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 查询学员学习进度（统计分析）
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public StudyLogListModel SearchInternalStudyLog(SearchStudyLogModel searchModel, int pageSize, int pageIndex)
        {
            StudyLogListModel listModel = new StudyLogListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@CourseID", searchModel.CourseID),
                new SqlParameter("@CertificateID", searchModel.CertificateID),
                new SqlParameter("@UserName", searchModel.UserName),
                new SqlParameter("@TeacherID", searchModel.TeacherID),
                new SqlParameter("@LoginID", searchModel.LoginID),
                new SqlParameter("@EntryFromDate", searchModel.EntryFromDate),
                new SqlParameter("@EntryToDate", searchModel.EntryToDate),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<StudyLogForListModel> list = new List<StudyLogForListModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_internal_getProgress", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        StudyLogForListModel slflm = new StudyLogForListModel();
                        slflm.ID = (int)dr["ID"];
                        slflm.LoginID = dr["LoginID"].ToString();
                        slflm.UserName = dr["UserName"].ToString();
                        slflm.Progress = (int)dr["Process"];
                        slflm.CourseID = (int)dr["CourseID"];
                        slflm.CourseName = dr["CourseName"].ToString();
                        slflm.CertificateName = dr["CertificateName"].ToString();
                        list.Add(slflm);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        #endregion

        #region 公共课程

        /// <summary>
        /// 获取用户学习记录
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="courseid"></param>
        /// <param name="isinternal"></param>
        /// <returns></returns>
        public IList<StudyLogModel> GetUserPublicStudyLog(int userid, int courseid, bool isinternal)
        {
            IList<StudyLogModel> list = new List<StudyLogModel>();
            string sql = "SELECT a.ClassID, a.UserID, b.ClassCode, b.CourseID, c.Name AS CourseName, c.SchoolHour, c.IsPublic, c.NeedLearn, c.IsInternalPublic, d.CertificateID FROM E_Public_Class2User AS a INNER JOIN E_Public_Class AS b ON a.ClassID = b.ID INNER JOIN E_Dict_Course AS c ON b.CourseID = c.ID INNER JOIN E_Dict_Course2Certificate AS d ON b.CourseID = d.CourseID WHERE a.UserID = " + userid;
            if (courseid != -1)
            {
                sql += " AND b.CourseID = " + courseid;
            }
            if (isinternal)
            {
                sql += " AND c.IsPublic = 0 AND c.IsInternalPublic = 1 ";
            }
            else
            {
                sql += " AND d.CertificateID = 1 AND c.IsPublic = 1 AND c.IsInternalPublic = 0 ";
            }
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    StudyLogModel model = new StudyLogModel();
                    model.CertificateID = (int)dr["CertificateID"];
                    model.CourseID = (int)dr["CourseID"];
                    model.CourseName = dr["CourseName"].ToString();
                    model.Progress = GetUserPublicStudyProgress((int)dr["UserID"], model.CourseID);
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 获取进度
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="courseid"></param>
        /// <returns></returns>
        public int GetUserPublicStudyProgress(int userid, int courseid)
        {
            string sql = "SELECT Process FROM E_Public_StudyLog WHERE (UserID = " + userid + ") AND (CourseID = " + courseid + ")";
            object rst = SqlHelper.ExecuteScalarBySql(sql);
            return rst == null ? 0 : Convert.ToInt32(rst);
        }

        /// <summary>
        /// 更新进度
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="courseid"></param>
        /// <param name="projectid"></param>
        /// <param name="process"></param>
        /// <param name="chapters"></param>
        /// <returns></returns>
        public int UpdateUserInternalPublicProcess(int userid, int courseid, int projectid, int process, string chapters)
        {
            string sql = "UPDATE E_Public_StudyLog SET Process = " + process + ", Chapters = '" + chapters + "' WHERE UserID = " + userid + " AND CourseID = " + courseid + " AND ProjectID = " + projectid;
            return SqlHelper.ExecuteNonQueryBySql(sql);
        }

        /// <summary>
        /// 获取学习记录简单信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public StudyLogModel GetPublicStudyShortLogByID(int courseid, int userid)
        {
            string sql = "SELECT ID,CourseID,Chapters FROM E_Public_StudyLog WHERE CourseID = " + courseid + " AND UserID = " + userid;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                StudyLogModel sim = new StudyLogModel();
                if (dr.Read())
                {
                    sim.ID = (int)dr["ID"];
                    sim.CourseID = (int)dr["CourseID"];
                    sim.Chapters = dr["Chapters"].ToString();
                }
                else
                    sim.ID = -1;
                return sim;
            }
        }

        #endregion

    }
}
